package case6

import (
	"fmt"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
	"log"
	"os"
	"study_gorm_v2/src/case5"
	"time"
)

func Test() {

	fmt.Println("🎯示例六：操作查询数据；包含打印sql日志")
	db, err := mysqlConnect()
	if err != nil {
		fmt.Println(err)
		return
	}

	var uc1 case5.UserCase5
	var ucArr1 []case5.UserCase5

	// 查询第一条数据
	//db.First(&uc1)

	// 获取一条记录，没有记录时返回 ErrRecordNotFound
	//db.Take(&uc1)
	// SELECT * FROM `user_case5` LIMIT 1

	// 获取最后一条记录
	//db.Last(&uc1)
	// SELECT * FROM `user_case5` ORDER BY `user_case5`.`id` DESC LIMIT 1

	// 根据主键查询
	//db.First(&uc1, 1)
	// SELECT * FROM `user_case5` WHERE `user_case5`.`id` = 1 ORDER BY `user_case5`.`id` LIMIT 1

	// 根据条件查询
	//db.First(&uc1, "user_name = ?", "lisi")
	// SELECT * FROM `user_case5` WHERE user_name = 'lisi' ORDER BY `user_case5`.`id` LIMIT 1

	// 条件查询
	//db.Where("user_name = ? And sex = ?", "lisi", true).First(&uc1)
	// SELECT * FROM `user_case5` WHERE user_name = 'lisi' And sex = true ORDER BY `user_case5`.`id` LIMIT 1

	//db.Where("user_name like ?", "%lisi%").First(&uc1)
	// SELECT * FROM `user_case5` WHERE user_name like '%lisi%' ORDER BY `user_case5`.`id` LIMIT 1

	// 获取所有
	//db.Find(&ucArr1)

	// 查询年龄大于等于22 并且状态="正常" 的数据
	//db.Find(&ucArr1, "age >= ? AND state = ?", 22, "正常")

	// 单表分组聚合查询
	//定义匿名结构体
	result := struct {
		Age int
		Num int
	}{}

	//定义匿名结构体数组
	resultArr := []struct {
		Age   int
		Num   int
		State string
	}{}

	// 统计age字段总和是？
	//db.Model(&case5.UserCase5{}).Select("sum(age) as age").Find(&result)
	//SELECT sum(age) as age FROM `user_case5`

	// 统计age大于12总和是
	//db.Model(&case5.UserCase5{}).Select("sum(age) as num").Group("age").Having("age > ?", 12).Find(&result)
	// SELECT sum(age) as num FROM `user_case5` GROUP BY `age` HAVING age > 12

	// 统计state状态的用户数量
	//db.Model(&case5.UserCase5{}).Select("state, COUNT(0) AS num").Group("state").Find(&resultArr)

	fmt.Println(db)
	fmt.Println(uc1)
	fmt.Println(ucArr1)
	fmt.Println(result)
	fmt.Println(resultArr)
}

// 初始化连接
func mysqlConnect() (*gorm.DB, error) {
	url := "root:123456@tcp(127.0.0.1:3306)/case1?charset=utf8mb4&parseTime=True&loc=Local"

	// 配置自定义 Logger 打印sql日志
	newLogger := logger.New(
		log.New(os.Stdout, "\r\n", log.LstdFlags), // io writer
		logger.Config{
			SlowThreshold:             time.Second, // 慢 SQL 阈值
			LogLevel:                  logger.Info, // 可以设置为不同级别 Silent-不打印任何日志 Error-只打印错误日志 Warn-打印慢查询和错误 Info-打印慢查询和错误
			IgnoreRecordNotFoundError: true,        // 忽略ErrRecordNotFound（记录未找到）错误
			Colorful:                  true,        // 彩色打印
		},
	)

	db, err := gorm.Open(mysql.Open(url), &gorm.Config{
		Logger: newLogger, // 设置 Logger
	})
	if err != nil {
		return nil, fmt.Errorf("mysql connect err: %v", err)
	}

	return db, nil
}
